Inserting Rows Using Core

In this chapter, we learn how to INSERT data using the SQLAlchemy Core approach.

Constructing SQL Expressions with insert()

First, you can create an INSERT statement like this:

>>> from sqlalchemy import insert

#  stmt is an instance of the Insert object.
>>> stmt = insert(user_table).values(name='spongebob', fullname="Spongebob Squarepants")
>>> print(stmt)
'INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)'

Here, user_table is the Table object we created in the previous chapter. We created it as follows.

from sqlalchemy import MetaData
from sqlalchemy import Table, Column, Integer, String

metadata = MetaData()
user_table = Table(
    'user_account',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(30)),
    Column('fullname', String),
)

Looking at stmt, you'll notice that the parameters have not yet been mapped. This can be checked after compile() it, as shown next.

>>> compiled = stmt.compile()
>>> print(compiled.params)
{'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}

Executing the Statement

Now, let's execute the INSERT statement we created above using the Core approach.

>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     conn.commit()

# The above code executes the following query.

BEGIN (implicit)
INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] ('spongebob', 'Spongebob Squarepants')
COMMIT

What information does the result contain, which is obtained from the return value of conn.execute(stmt)? result is a CursorResult (opens new window) object. It holds various information about the execution results, particularly the Row (opens new window) objects that contain data rows.

Since we have just inserted data, we can check the primary key value of the inserted data as follows.

>>> result.inserted_primary_key  # This is also a Row object.
(1, )  # As the primary key can be composed of multiple columns, it is represented as a tuple.

Passing INSERT Parameters to Connection.execute()

Above, we created a statement that included values along with insert.

>>> stmt = insert(user_table).values(name='spongebob', fullname="Spongebob Squarepants")

However, besides this method, you can also execute an INSERT statement by passing parameters to the Connection.execute() method. The official documentation suggests this as a more common approach.

>>> with engine.connect() as conn:
...     result = conn.execute(
...         insert(user_table),
...         [
...             {"name": "sandy", "fullname": "Sandy Cheeks"},
...             {"name": "patrick", "fullname": "Patrick Star"}
...         ]
...     )
...     conn.commit()

The official documentation also explains how to execute statements including subqueries in a separate section. However, it has been deemed not entirely suitable for the tutorial content and is not included in this text. For those interested in this topic, please refer to the original documentation (opens new window).

Insert.from_select()

Sometimes you need a query to INSERT rows that are received from a SELECT statement, as in the following example.

Such cases can be written as shown in the following code.

>>> select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
>>> insert_stmt = insert(address_table).from_select(
...     ["user_id", "email_address"], select_stmt
... )
>>> print(insert_stmt)
"""
INSERT INTO address (user_id, email_address)
SELECT user_account.id, user_account.name || :name_1 AS anon_1
FROM user_account
"""

Insert.returning()

There are situations where you need to receive the value of the processed rows from the database after query processing. This is known as the RETURNING syntax. For an introduction to this, it would be good to read this wiki (opens new window).

In SQLAlchemy Core, this RETURNING syntax can be written as follows.

>>> insert_stmt = insert(address_table).returning(address_table.c.id, address_table.c.email_address)
>>> print(insert_stmt)
"""
INSERT INTO address (id, user_id, email_address)
VALUES (:id, :user_id, :email_address)
RETURNING address.id, address.email_address
"""